USE [master]
GO
/****** Object:  Database [CRM]    Script Date: 6/19/2012 2:16:03 AM ******/
CREATE DATABASE [CRM]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'CRM', FILENAME = N'C:\Users\Public\BD\CRM\CRM.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'CRM_log', FILENAME = N'C:\Users\Public\BD\CRM\CRM_log.ldf' , SIZE = 4672KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
ALTER DATABASE [CRM] SET COMPATIBILITY_LEVEL = 100
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [CRM].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO
ALTER DATABASE [CRM] SET ANSI_NULL_DEFAULT OFF 
GO
ALTER DATABASE [CRM] SET ANSI_NULLS OFF 
GO
ALTER DATABASE [CRM] SET ANSI_PADDING OFF 
GO
ALTER DATABASE [CRM] SET ANSI_WARNINGS OFF 
GO
ALTER DATABASE [CRM] SET ARITHABORT OFF 
GO
ALTER DATABASE [CRM] SET AUTO_CLOSE OFF 
GO
ALTER DATABASE [CRM] SET AUTO_CREATE_STATISTICS ON 
GO
ALTER DATABASE [CRM] SET AUTO_SHRINK OFF 
GO
ALTER DATABASE [CRM] SET AUTO_UPDATE_STATISTICS ON 
GO
ALTER DATABASE [CRM] SET CURSOR_CLOSE_ON_COMMIT OFF 
GO
ALTER DATABASE [CRM] SET CURSOR_DEFAULT  GLOBAL 
GO
ALTER DATABASE [CRM] SET CONCAT_NULL_YIELDS_NULL OFF 
GO
ALTER DATABASE [CRM] SET NUMERIC_ROUNDABORT OFF 
GO
ALTER DATABASE [CRM] SET QUOTED_IDENTIFIER OFF 
GO
ALTER DATABASE [CRM] SET RECURSIVE_TRIGGERS OFF 
GO
ALTER DATABASE [CRM] SET  DISABLE_BROKER 
GO
ALTER DATABASE [CRM] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
GO
ALTER DATABASE [CRM] SET DATE_CORRELATION_OPTIMIZATION OFF 
GO
ALTER DATABASE [CRM] SET TRUSTWORTHY OFF 
GO
ALTER DATABASE [CRM] SET ALLOW_SNAPSHOT_ISOLATION OFF 
GO
ALTER DATABASE [CRM] SET PARAMETERIZATION SIMPLE 
GO
ALTER DATABASE [CRM] SET READ_COMMITTED_SNAPSHOT OFF 
GO
ALTER DATABASE [CRM] SET HONOR_BROKER_PRIORITY OFF 
GO
ALTER DATABASE [CRM] SET RECOVERY FULL 
GO
ALTER DATABASE [CRM] SET  MULTI_USER 
GO
ALTER DATABASE [CRM] SET PAGE_VERIFY CHECKSUM  
GO
ALTER DATABASE [CRM] SET DB_CHAINING OFF 
GO
ALTER DATABASE [CRM] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF ) 
GO
ALTER DATABASE [CRM] SET TARGET_RECOVERY_TIME = 0 SECONDS 
GO
EXEC sys.sp_db_vardecimal_storage_format N'CRM', N'ON'
GO
USE [CRM]
GO
/****** Object:  StoredProcedure [dbo].[CREAR_CANAL]    Script Date: 6/19/2012 2:16:03 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[CREAR_CANAL]
(
	@i_nombre     VARCHAR(50),
	@i_medio	  DECIMAL(18,0),
	@i_usu		  int,
	@i_bkp		  int
) AS
   
BEGIN
	
	INSERT INTO CANALES (canal_nombre, canal_medio, canal_usu, canal_bkp) 
	VALUES (@i_nombre, @i_medio, @i_usu, @i_bkp)
	
	SELECT MAX(canal_id) FROM CANALES;

END
GO
/****** Object:  StoredProcedure [dbo].[CREAR_OBJETIVO]    Script Date: 6/19/2012 2:16:03 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[CREAR_OBJETIVO]
(
	@i_nombre     VARCHAR(50),
	@i_valor	  DECIMAL(18,0),
	@i_flag		  int,
	@i_cod		  int
) AS
   
BEGIN
	
	INSERT INTO OBJETIVOS (obje_nombre, obje_valor, obje_flag_general, obje_id_general) 
	VALUES (@i_nombre, @i_valor, @i_flag, @i_cod)
	
	SELECT MAX(obje_id) FROM OBJETIVOS;

END
GO
/****** Object:  StoredProcedure [dbo].[EDITAR_CANAL]    Script Date: 6/19/2012 2:16:03 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[EDITAR_CANAL]
(
	@i_canal	  int,
	@i_nombre     VARCHAR(50),
	@i_medio	  DECIMAL(18,0),
	@i_usu		  int,
	@i_bkp		  int
) AS
   
BEGIN
	
	UPDATE CANALES SET canal_nombre=@i_nombre, canal_medio=@i_medio, canal_usu=@i_usu, canal_bkp=@i_bkp 
	WHERE canal_id=@i_canal 
	
END
GO
/****** Object:  StoredProcedure [dbo].[EDITAR_OBJETIVO]    Script Date: 6/19/2012 2:16:03 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[EDITAR_OBJETIVO]
(
	@i_id    int,
	@i_nombre     VARCHAR(50),
	@i_valor	  DECIMAL(18,0),
	@i_flag		  int,
	@i_cod		  int
) AS
   
BEGIN
		
	UPDATE OBJETIVOS SET obje_nombre=@i_nombre, obje_valor=@i_valor, 
		   obje_flag_general=@i_flag, obje_id_general=@i_cod
	WHERE obje_id=@i_id;

END
GO
/****** Object:  StoredProcedure [dbo].[ELIMINAR_CANAL]    Script Date: 6/19/2012 2:16:03 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[ELIMINAR_CANAL]
(
	@i_id    int
) AS
   
BEGIN
		
	DELETE FROM CANALES WHERE canal_id=@i_id

END
GO
/****** Object:  StoredProcedure [dbo].[ELIMINAR_OBJETIVO]    Script Date: 6/19/2012 2:16:03 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[ELIMINAR_OBJETIVO]
(
	@i_id    int
) AS
   
BEGIN
		
	DELETE FROM OBJETIVOS WHERE (obje_id=@i_id OR obje_id_general=@i_id)

END
GO
/****** Object:  StoredProcedure [dbo].[INICIAR_SESION]    Script Date: 6/19/2012 2:16:03 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[INICIAR_SESION]
(
	@i_username         VARCHAR(50),
	@i_userpass         VARCHAR(50)
) AS
   
BEGIN

	SELECT usu_id, usu_name, usu_pass FROM USUARIOS 
	WHERE usu_name=@i_username AND usu_pass=@i_userpass

END
GO
/****** Object:  StoredProcedure [dbo].[LISTAR_CAMPANAS]    Script Date: 6/19/2012 2:16:03 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[LISTAR_CAMPANAS]
 AS
   
BEGIN
	SELECT campa_id, campa_nombre FROM CAMPANAS

END


GO
/****** Object:  StoredProcedure [dbo].[LISTAR_CANALES]    Script Date: 6/19/2012 2:16:03 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[LISTAR_CANALES]
 AS
   
BEGIN
	SELECT * FROM CANALES

END


GO
/****** Object:  StoredProcedure [dbo].[LISTAR_OBJETIVOS]    Script Date: 6/19/2012 2:16:03 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[LISTAR_OBJETIVOS]
 AS
   
BEGIN	
	SELECT OBJE_ID, OBJE_NOMBRE, OBJE_VALOR, OBJE_FLAG_GENERAL FROM OBJETIVOS WHERE OBJE_FLAG_GENERAL=1;
END
GO
/****** Object:  StoredProcedure [dbo].[LISTAR_USUARIOS]    Script Date: 6/19/2012 2:16:03 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[LISTAR_USUARIOS]
 AS
   
BEGIN
	SELECT usu_id, usu_name, usu_pass FROM USUARIOS ORDER BY usu_name
END


GO
/****** Object:  StoredProcedure [dbo].[OBTENER_CANAL]    Script Date: 6/19/2012 2:16:03 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[OBTENER_CANAL]
(
	@i_canal	  int
) AS
   
BEGIN
	
	SELECT canal_id, canal_nombre, canal_medio, canal_usu, canal_bkp FROM CANALES WHERE canal_id=@i_canal

END
GO
/****** Object:  StoredProcedure [dbo].[OBTENER_OBJETIVO]    Script Date: 6/19/2012 2:16:03 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[OBTENER_OBJETIVO]
(
	@i_objeid     INT
)
 AS
   
BEGIN	
	SELECT OBJE_ID, OBJE_NOMBRE, OBJE_VALOR, OBJE_FLAG_GENERAL FROM OBJETIVOS 
	WHERE (OBJE_ID=@i_objeid OR obje_id_general=@i_objeid) ORDER BY 1;
END
GO
/****** Object:  Table [dbo].[Campanas]    Script Date: 6/19/2012 2:16:03 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Campanas](
	[campa_id] [int] IDENTITY(1,1) NOT NULL,
	[campa_nombre] [varchar](50) NOT NULL,
 CONSTRAINT [PK_Campanas] PRIMARY KEY CLUSTERED 
(
	[campa_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[Canales]    Script Date: 6/19/2012 2:16:03 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Canales](
	[canal_id] [int] IDENTITY(1,1) NOT NULL,
	[canal_nombre] [nvarchar](50) NOT NULL,
	[canal_medio] [int] NOT NULL,
	[canal_usu] [int] NOT NULL,
	[canal_bkp] [int] NOT NULL,
 CONSTRAINT [PK_Canales] PRIMARY KEY CLUSTERED 
(
	[canal_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[Objetivos]    Script Date: 6/19/2012 2:16:03 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Objetivos](
	[obje_id] [int] IDENTITY(1,1) NOT NULL,
	[obje_nombre] [nvarchar](50) NOT NULL,
	[obje_valor] [decimal](18, 0) NOT NULL,
	[obje_flag_general] [int] NOT NULL,
	[obje_id_general] [int] NOT NULL,
 CONSTRAINT [PK_Objetivos] PRIMARY KEY CLUSTERED 
(
	[obje_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[Usuarios]    Script Date: 6/19/2012 2:16:03 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Usuarios](
	[usu_id] [int] NOT NULL,
	[usu_name] [varchar](50) NOT NULL,
	[usu_pass] [varchar](50) NOT NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
USE [master]
GO
ALTER DATABASE [CRM] SET  READ_WRITE 
GO
